
--Declare
--@StartTime Datetime,
--@Rpt_JobName	Varchar(300),
--@Rpt_JobID		Char(36)

----test variables
--Set @StartTime = DATEADD(HH,-24,Getdate())
--Set @Rpt_JobID = '4BA7E04B-141D-4702-B92D-D222FEDB8336'
----Set @Rpt_JobName = 'VDBA - Failed Jobs'


--Exec master.dbo.VDBA_FailedJobs 

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[VDBA_FailedJobs]    Script Date: 1/12/2015 5:09:13 PM ******/
DROP PROCEDURE [dbo].[VDBA_FailedJobs]
GO

Create Procedure VDBA_FailedJobs (@StartTime Datetime = Null,
								@Rpt_JobName	Varchar(300) = Null ,
								@Rpt_JobID		Uniqueidentifier = Null,
								@Check_JobName	Varchar(300) = '%' ,
								@Check_JobID	VarChar(36)= '%')
As
Begin

/*
VDBA_FailedJobs
Returns a list of jobs that failed on thier last run.
The run time for the oldest failed job to report is determined by the inputs.

@StartTime	DateTime		Returns Failed jobs run after this datetime.
@Rpt_JobName	Varchar(300)	Returns failed jobs that ran after the last sucsesfull run of the job with this name.
@Rpt_JobID		Char(36)		Returns failed jobs that ran after the last sucsesfull run of the job with this Job ID.

@Check_JobName	Varchar(300) Checks for specific jobs whose name matches the pattern, uses wildcards. Returns all failed jobs if blank.
@Check_JobID	VarChar(36)= Checks for specific jobs Job ID name matches the pattern, uses wildcards. Returns all failed jobs if blank.

Notes:
Only jobs whose last run failed are reported, Jobs that failed then sucseeded during the timeframe are not reported.
Results are limited by the more restrictive of @StartTime and @Rpt_JobID	
@Rpt_JobName is ignored if @Rpt_JobID is set.
No inputs returns all failed jobs recorded for the instance.

Author: David Rugh, Xtivia

Change Log:
01/13/2015	Initial Release	drugh@xtivia.com
*/

	Set Nocount on
--Clean input variables
Set	@Rpt_JobName	= Replace(@Rpt_JobName,';','')
Set	@Rpt_JobID		= Replace(@Rpt_JobID,';','')
Set	@Check_JobName	= Replace(@Check_JobName,';','')
Set	@Check_JobID	= Replace(@Check_JobID,';','')


	Declare 
	@JobStartDate		Int,
	@JobStartTime		Int,
	@JobStartInstance	Int,
	@FailedCount		Int,
	@VDBA_Header		Varchar(300),
	@VDBA_EOL			Varchar(120)
	--@dt					Varchar(30),
	--@Mail_Id			varchar(255),


	--Initialize local variables from the VDBA header
 		--Get header and timestamp
		--TimeStamp on each row of an INFO file neet to match the TimeStamp in @VDBA_Header
		Declare 
			@HeaderCounter		Int,
			@HeaderIndex		Int

		Set @VDBA_Header = master.dbo.GetVDBAHeader('Info')

		--Set @dt from the date stamp located between the first and second '|', and the VDBA Node ID between the 5th and 6th '|' in @VDBA_Header
		Set @HeaderIndex = 0
		Set @HeaderCounter = CharIndex('|',@VDBA_Header)

		While @HeaderIndex < Len(@VDBA_HEader) - Len(Replace(@VDBA_Header,'|','')) --difference between @VDBA_Header with '|' and without '|' to prevent loops on invalid @VDBA_Header.
		--While @HeaderCounter < Len(@VDBA_Header)
		Begin
			If Substring(@VDBA_Header,@HeaderCounter,1) = '|'
				Begin
					Set @HeaderIndex = @HeaderIndex + 1
					If @HeaderIndex = 1 Set @VDBA_EOL = SUBSTRING(@VDBA_Header, @HeaderCounter + 1, CharIndex('|',@VDBA_Header,@HeaderCounter + 1) - @HeaderCounter)
					If @HeaderIndex = 5 Set @VDBA_EOL = @VDBA_EOL + SUBSTRING(@VDBA_Header, @HeaderCounter + 1, CharIndex('|',@VDBA_Header,@HeaderCounter + 1) - @HeaderCounter)
				End
		--Increment counters
		Set @HeaderCounter = @HeaderCounter + 1
		End

	--Determine how far back to retrieve failed job info.
	--Restrict the the most recent @StartTime or sucsesfull run of job defined by @Rpt_JobName or @Rpt_JobID

	--Determine @Rpt_JobID
		If @Rpt_JobID is null --@job_ID needs to be defined, try to find it based on @job_name
			Select @Rpt_JobID = Job_id 
			from msdb.dbo.sysjobs 
			where name = @Rpt_JobName

		If @Rpt_JobID is null --IF @Job_id is still null then previous select statement returned Null because @Job_name was not a valid name or null.
			Set @JobStartInstance = 0 
		Else --Find the Instance number from the last sucsesfull run of the job
		Select @JobStartInstance = Max(sjh.instance_id )
			From msdb.dbo.sysjobhistory sjh
			Where sjh.job_id = @Rpt_JobID and sjh.run_status = 1 


	--Convert StartTime to 2 interger values YYYYMMDD and HHMMSS
	--Select Cast(Convert(Char(8),@StartTime,112) as int) as date, Cast(Replace(Convert(VarChar(8),@StartTime,108),':','') as int) as time

	If @StartTime is not null
	Begin
		Set @JobStartDate = Cast(Convert(Char(8),@StartTime,112) as int)
		Set @JobStartTime = Cast(Replace(Convert(VarChar(8),@StartTime,108),':','') as int)
	End
	Else
	Begin
		Set @JobStartDate = 0
		Set @JobStartTime = 0
	End


	--Determine if report needs to be sent
	Select @FailedCount = count(sj.name)
	from msdb.dbo.sysjobhistory sjh
	Join
	(select Max(instance_id) as Instance_id_Max,
	 job_id, 
	 step_id
	from msdb.dbo.sysjobhistory
	Group by job_id, step_id) sjhMax on sjhMax.Instance_id_Max = sjh.instance_id
	Join msdb.dbo.sysjobs sj on sj.job_id = sjh.job_id
	Where 
		 sjh.step_id = 0
		 and sjh.instance_id >= @JobStartInstance 
		 and (sjh.run_date > @JobStartDate or (sjh.run_date = @JobStartDate and sjh.run_time >= @JobStartTime))
		 And sjh.job_id like @Check_JobID
		 And sj.name like @Check_JobName
		 And sjh.run_status not in (1,4)

	 If @FailedCount > 0 

	 	 Begin 
	 --Create the info file
		Select @VDBA_Header
		Union all
		Select 
		'CLIENT_ALERTS|[' + Left(Replace(sj.name,'|','') + '] ' + 
		Case
			When sjh.message like '%The last step to run was step%' Then
				--SUBSTRING(sjh.message,Patindex('%The last step to run was step%',sjh.message) + 24, Patindex('%The last step to run was step%',sjh.message) + 24)
				'Failed at ' + SUBSTRING(sjh.message,Patindex('%The last step to run was step%',sjh.message) + 24, 100)
			When sjh.message like '%Unable to determine if the owner%' Then
				'Failed with "Unable to determine if the owner has server access"'
			Else	
				'Failed to start'
		End	 + ' at ' +
		Left(Cast(sjh.run_Date as Char(8)), 4)+'-'+Substring(Cast(sjh.run_Date as Char(8)),5,2)+'-'+Right(Cast(sjh.run_Date as Char(8)),2) + ' ' +
		Left(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),2) + ':' +
		Substring(Replicate('0',6-Len(Cast(sjh.run_time as Varchar(6)))) + Cast(sjh.run_time as Varchar(6)),3,2), 150)+ 
		Case --report 282 for failed VDBA jobs, 309 for all other jobs
			When Upper(sj.name) like 'VDBA -%' then
			'|282|'
		Else		 
			'|309|'
		End + @VDBA_EOL

		from msdb.dbo.sysjobhistory sjh
		Join
		(select Max(instance_id) as Instance_id_Max,
		 job_id, 
		 step_id
		from msdb.dbo.sysjobhistory
		Group by job_id, step_id) sjhMax on sjhMax.Instance_id_Max = sjh.instance_id
		Join msdb.dbo.sysjobs sj on sj.job_id = sjh.job_id
		Where 
		 sjh.step_id = 0
		 and sjh.instance_id >= @JobStartInstance 
		 and (sjh.run_date > @JobStartDate or (sjh.run_date = @JobStartDate and sjh.run_time >= @JobStartTime))
		 And sjh.job_id like @Check_JobID
		 And sj.name like @Check_JobName 
		 And sjh.run_status not in (1,4)
	End 
End

.............
GO


--Exec master.dbo.VDBA_FailedJobs @StartTime = '2015-01-13 12:19:45.290'


Exec master.dbo.VDBA_FailedJobs 

--select @@servername
--select @@SERVICENAME

--Exec master.dbo.VDBA_FailedJobs @Check_JobName = 'LSAlert_Col39'

--Exec master.dbo.VDBA_FailedJobs @Check_JobName = '%Col39%'

--Exec master.dbo.VDBA_FailedJobs @Check_JobID = '107F42D4-CF1C-4160-913B-0A71E339E91A'
--Exec master.dbo.VDBA_FailedJobs @Check_JobID = '4CBD8E91-E529-459D-AAEB-7FD15FD10256'

Exec master.dbo.VDBA_FailedJobs  @Rpt_JobID = 0x4BE0A74B1D140247B92DD222FEDB8336
Exec master.dbo.VDBA_FailedJobs  @Rpt_JobName = 'VDBA - Failed Jobs'
--Exec master.dbo.VDBA_FailedJobs @StartTime = '2015-01-13 12:08:45.290'

--Select GetDate()


----declare @Check_JobID		VarChar(36)
----Set @Check_JobID  = '%'

----select * from msdb.dbo.sysjobhistory where run_status not in (1,4) and job_id like @Check_JobID

--select * from msdb.dbo.sysjobs where name = 'LSAlert_Col39'


--select
--SUBSTRING(sjh.message,Patindex('%The last step to run was step%',sjh.message) + 24, 100)
-- ,* from msdb.dbo.sysjobhistory sjh
--  where run_status not in (1,4) and message like '%The last step to run was step%'

--  select
--SUBSTRING(sjh.message,Patindex('%The last step to run was step%',sjh.message) + 24, 100)
-- ,* from msdb.dbo.sysjobhistory sjh
--  where run_status not in (1,4) and message like '%The last step to run was step%'